{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Request successful\n",
      "Request successful\n",
      "BMI:  BMI: 27.0\n",
      "10-Year Major Osteoporotic Fracture Risk:  33\n",
      "10-Year Hip Fracture Risk:  30\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "# 设置目标URL\n",
    "url = \"https://frax.shef.ac.uk/FRAX/tool.aspx?country=2\"\n",
    "\n",
    "# 使用requests会话\n",
    "with requests.Session() as session:\n",
    "    # 获取初始页面以获取隐藏字段的值\n",
    "    initial_page = session.get(url)\n",
    "    \n",
    "    # 检查请求是否成功\n",
    "    if initial_page.status_code == 200:\n",
    "        # 从页面中提取隐藏字段的值\n",
    "        soup = BeautifulSoup(initial_page.text, 'html.parser')\n",
    "        viewstate = soup.find(id='__VIEWSTATE')['value']\n",
    "        eventvalidation = soup.find(id='__EVENTVALIDATION')['value']\n",
    "        viewstategenerator = soup.find(id='__VIEWSTATEGENERATOR')['value']\n",
    "        hdn_auth_code = soup.find(id='ctl00_ContentPlaceHolder1_hdnAuthCode')['value']\n",
    "        \n",
    "        # 模拟表单数据\n",
    "        data = {\n",
    "            'ctl00$ScriptManager1': 'ctl00$ContentPlaceHolder1$updResult|ctl00$ContentPlaceHolder1$btnCalculate',\n",
    "            'ctl00_ScriptManager1_TSM': ';;System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35:en-US:18f1b484-bbc5-4e2e-8ca4-477603537f34:ea597d4b:b25378d2',\n",
    "            '__EVENTTARGET': '',\n",
    "            '__EVENTARGUMENT': '',\n",
    "            '__VIEWSTATE': viewstate,\n",
    "            '__VIEWSTATEGENERATOR': viewstategenerator,\n",
    "            '__EVENTVALIDATION': eventvalidation,\n",
    "             'language': 'en',\n",
    "#             'country':'2',\n",
    "            'ctl00$ContentPlaceHolder1$hdnethnicity': '2',\n",
    "            'ctl00$ContentPlaceHolder1$hdnpreviousfracture': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnpfracturehip': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdncurrentsmoker': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnglucocorticoids': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnarthritis': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnosteoporosis': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnalcohol': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnbmd': 'T',\n",
    "            'ctl00$ContentPlaceHolder1$hdnthescore': '-5',#测试0.35 -5\n",
    "            'ctl00$ContentPlaceHolder1$hdnsex': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnbmi': '',\n",
    "            'ctl00$ContentPlaceHolder1$hdnAuthCode': hdn_auth_code,\n",
    "            'ctl00$ContentPlaceHolder1$nameid': '',\n",
    "            'ctl00$ContentPlaceHolder1$toolage': '55',  # 示例年龄\n",
    "            'ctl00$ContentPlaceHolder1$toolagehidden': '55',\n",
    "            'ctl00$ContentPlaceHolder1$year': '',\n",
    "            'ctl00$ContentPlaceHolder1$month': '',\n",
    "            'ctl00$ContentPlaceHolder1$day': '',\n",
    "            'ctl00$ContentPlaceHolder1$sex': 'male',  # 示例性别\n",
    "            'ctl00$ContentPlaceHolder1$toolweight': '50',  # 示例体重\n",
    "            'ctl00$ContentPlaceHolder1$toolweighthidden': '50',\n",
    "            'ctl00$ContentPlaceHolder1$ht': '136',  # 示例身高\n",
    "            'ctl00$ContentPlaceHolder1$toolheighthidden': '136',\n",
    "            'ctl00$ContentPlaceHolder1$facture': 'no',\n",
    "            'ctl00$ContentPlaceHolder1$facture_hip': 'no',\n",
    "            'ctl00$ContentPlaceHolder1$smoking': 'no',\n",
    "            'ctl00$ContentPlaceHolder1$glu': 'no',\n",
    "            'ctl00$ContentPlaceHolder1$rhe_art': 'no',\n",
    "            'ctl00$ContentPlaceHolder1$sec_ost': 'yes',\n",
    "            'ctl00$ContentPlaceHolder1$alcohol': 'no',\n",
    "            'dxa': 'GE-Lunar',  # 示例DXA\n",
    "            'ctl00$ContentPlaceHolder1$bmd_input': '0.35',  # 示例BMD值\n",
    "            '__ASYNCPOST': 'true',\n",
    "            'ctl00$ContentPlaceHolder1$btnCalculate': 'Calculate'\n",
    "        }\n",
    "\n",
    "        # 设置请求头\n",
    "        headers = {\n",
    "            'authority': 'frax.shef.ac.uk',\n",
    "            'method': 'POST',\n",
    "            'path': '/FRAX/tool.aspx/GetCountryHits',\n",
    "            'scheme': 'https',\n",
    "            'accept': '*/*',\n",
    "            'accept-encoding': 'gzip, deflate, br, zstd',\n",
    "            'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8',\n",
    "            'content-length': str(len(str(data))),\n",
    "            'content-type': 'application/x-www-form-urlencoded; charset=UTF-8',\n",
    "            'cookie': 'country=2; lang=chs; ASP.NET_SessionId=rkpwszbi4jdfbv2wvhoztik2; b-user-id=2fa2a736-7a21-81a0-e74a-13c7b61df511; _ga=GA1.3.140746098.1717039493; _gid=GA1.3.240733912.1717039493; _ga_9QM6GC3ENW=GS1.3.1717045018.2.1.1717046357.0.0.0',\n",
    "            'origin': 'https://frax.shef.ac.uk',\n",
    "            'priority': 'u=1, i',\n",
    "            'referer': 'https://frax.shef.ac.uk/FRAX/tool.aspx',\n",
    "            'sec-ch-ua': '\"Google Chrome\";v=\"125\", \"Chromium\";v=\"125\", \"Not.A/Brand\";v=\"24\"',\n",
    "            'sec-ch-ua-mobile': '?0',\n",
    "            'sec-ch-ua-platform': '\"Windows\"',\n",
    "            'sec-fetch-dest': 'empty',\n",
    "            'sec-fetch-mode': 'cors',\n",
    "            'sec-fetch-site': 'same-origin',\n",
    "            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36',\n",
    "            'x-requested-with': 'XMLHttpRequest'\n",
    "        }\n",
    "\n",
    "        # 发送POST请求\n",
    "        response = session.post(url, data=data, headers=headers)\n",
    "        \n",
    "        # 检查请求是否成功\n",
    "        if response.status_code == 200:\n",
    "            print(\"Request successful\")\n",
    "            print(\"Request successful\")\n",
    "        \n",
    "            # 解析更新面板HTML内容\n",
    "            \n",
    "            update_soup = BeautifulSoup(response.text, 'html.parser')\n",
    "                \n",
    "            # 查找并提取结果\n",
    "            result_bmi = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbbmi\")\n",
    "            result_major_osteoporotic = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbrs1\")\n",
    "            result_hip_fracture = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbrs2\")\n",
    "                \n",
    "            if result_bmi and result_major_osteoporotic and result_hip_fracture:\n",
    "                    print(\"BMI: \", result_bmi.text.strip())\n",
    "                    print(\"10-Year Major Osteoporotic Fracture Risk: \", result_major_osteoporotic.text.strip())\n",
    "                    print(\"10-Year Hip Fracture Risk: \", result_hip_fracture.text.strip())\n",
    "            else:\n",
    "                    print(\"Result not found\")\n",
    "        else:\n",
    "            print(\"错误发生\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "q1:试一试把t分数 空着 ,只填骨密度 不行 \n",
    "\n",
    "t如何计算\n",
    "①\n",
    "T-score=$\\frac{BMD-均值}{标准差}$\n",
    "联立两个方程0.35:-5 0.55:-3.5  用1.03:-0.1验证 --改进--> 最小二乘法\n",
    "②\n",
    "先遍历BMD\n",
    "\n",
    "q2:女性男性 data有额外区别吗 没看出明显的\n",
    "\n",
    "验证一下 45 1 60 180 011110 0.55\n",
    "10-Year Major Osteoporotic Fracture Risk:  6.0\n",
    "10-Year Hip Fracture Risk:  4.1 而网站 25 18 原因是:是只改了yes or no 而真正有用的是上面的01 现在没问题了\n",
    "此外post里的'GE-Lunar',  # 示例DXA tl00$ContentPlaceHolder1$bmd_input': '0.50', 也是他计算不用的,他实际只用t分数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 完全正确模板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Request successful\n",
      "BMI:  BMI: 18.5\n",
      "10-Year Major Osteoporotic Fracture Risk:  25\n",
      "10-Year Hip Fracture Risk:  18\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "# 设置目标URL\n",
    "url = \"https://frax.shef.ac.uk/FRAX/tool.aspx?country=2\"\n",
    "\n",
    "# 使用requests会话\n",
    "with requests.Session() as session:\n",
    "    # 获取初始页面以获取隐藏字段的值\n",
    "    initial_page = session.get(url)\n",
    "    \n",
    "    # 检查请求是否成功\n",
    "    if initial_page.status_code == 200:\n",
    "        # 从页面中提取隐藏字段的值\n",
    "        soup = BeautifulSoup(initial_page.text, 'html.parser')\n",
    "        viewstate = soup.find(id='__VIEWSTATE')['value']\n",
    "        eventvalidation = soup.find(id='__EVENTVALIDATION')['value']\n",
    "        viewstategenerator = soup.find(id='__VIEWSTATEGENERATOR')['value']\n",
    "        hdn_auth_code = soup.find(id='ctl00_ContentPlaceHolder1_hdnAuthCode')['value']\n",
    "        \n",
    "        # 模拟表单数据\n",
    "        data = {\n",
    "            'ctl00$ScriptManager1': 'ctl00$ContentPlaceHolder1$updResult|ctl00$ContentPlaceHolder1$btnCalculate',\n",
    "            'ctl00_ScriptManager1_TSM': ';;System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35:en-US:18f1b484-bbc5-4e2e-8ca4-477603537f34:ea597d4b:b25378d2',\n",
    "            '__EVENTTARGET': '',\n",
    "            '__EVENTARGUMENT': '',\n",
    "            '__VIEWSTATE': viewstate,\n",
    "            '__VIEWSTATEGENERATOR': viewstategenerator,\n",
    "            '__EVENTVALIDATION': eventvalidation,\n",
    "             'language': 'en',\n",
    "#             'country':'2',\n",
    "            'ctl00$ContentPlaceHolder1$hdnethnicity': '2',\n",
    "            'ctl00$ContentPlaceHolder1$hdnpreviousfracture': '0',##* 这些0/1是必填\n",
    "            'ctl00$ContentPlaceHolder1$hdnpfracturehip': '1',\n",
    "            'ctl00$ContentPlaceHolder1$hdncurrentsmoker': '1',\n",
    "            'ctl00$ContentPlaceHolder1$hdnglucocorticoids': '1',\n",
    "            'ctl00$ContentPlaceHolder1$hdnarthritis': '1',\n",
    "            'ctl00$ContentPlaceHolder1$hdnosteoporosis': '1',\n",
    "            'ctl00$ContentPlaceHolder1$hdnalcohol': '0',\n",
    "            'ctl00$ContentPlaceHolder1$hdnbmd': 'T',\n",
    "            'ctl00$ContentPlaceHolder1$hdnthescore': '-3.495656465465465465',#测试0.35 -5 0.55:-3.5##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$hdnsex': '1',##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$hdnbmi': '',\n",
    "            'ctl00$ContentPlaceHolder1$hdnAuthCode': hdn_auth_code,\n",
    "            'ctl00$ContentPlaceHolder1$nameid': '',\n",
    "            'ctl00$ContentPlaceHolder1$toolage': '45',  # 示例年龄##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$toolagehidden': '45',##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$year': '',\n",
    "            'ctl00$ContentPlaceHolder1$month': '',\n",
    "            'ctl00$ContentPlaceHolder1$day': '',\n",
    "            'ctl00$ContentPlaceHolder1$sex': 'fema阿达的le',  # 示例性别##* 其实不是必填\n",
    "            'ctl00$ContentPlaceHolder1$toolweight': '60',  # 示例体重##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$toolweighthidden': '60',##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$ht': '180',  # 示例身高\n",
    "            'ctl00$ContentPlaceHolder1$toolheighthidden': '180',##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$facture': 'no',\n",
    "            'ctl00$ContentPlaceHolder1$facture_hip': 'yes',\n",
    "            'ctl00$ContentPlaceHolder1$smoking': 'yes',\n",
    "            'ctl00$ContentPlaceHolder1$glu': 'yes',\n",
    "            'ctl00$ContentPlaceHolder1$rhe_art': 'yes',\n",
    "            'ctl00$ContentPlaceHolder1$sec_ost': '这些yesno根本没用',\n",
    "            'ctl00$ContentPlaceHolder1$alcohol': 'no',\n",
    "            'dxa': 'GE-Lunar',  # 示例DXA##* 是必填\n",
    "            'ctl00$ContentPlaceHolder1$bmd_input': '0.00',  # 示例BMD值 \n",
    "            '__ASYNCPOST': 'true',\n",
    "            'ctl00$ContentPlaceHolder1$btnCalculate': 'Calculate'\n",
    "        }\n",
    "\n",
    "        # 设置请求头\n",
    "        headers = {\n",
    "            'authority': 'frax.shef.ac.uk',\n",
    "            'method': 'POST',\n",
    "            'path': '/FRAX/tool.aspx/GetCountryHits',\n",
    "            'scheme': 'https',\n",
    "            'accept': '*/*',\n",
    "            'accept-encoding': 'gzip, deflate, br, zstd',\n",
    "            'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8',\n",
    "            'content-length': str(len(str(data))),\n",
    "            'content-type': 'application/x-www-form-urlencoded; charset=UTF-8',\n",
    "            'cookie': 'country=2; lang=chs; ASP.NET_SessionId=rkpwszbi4jdfbv2wvhoztik2; b-user-id=2fa2a736-7a21-81a0-e74a-13c7b61df511; _ga=GA1.3.140746098.1717039493; _gid=GA1.3.240733912.1717039493; _ga_9QM6GC3ENW=GS1.3.1717045018.2.1.1717046357.0.0.0',\n",
    "            'origin': 'https://frax.shef.ac.uk',\n",
    "            'priority': 'u=1, i',\n",
    "            'referer': 'https://frax.shef.ac.uk/FRAX/tool.aspx',\n",
    "            'sec-ch-ua': '\"Google Chrome\";v=\"125\", \"Chromium\";v=\"125\", \"Not.A/Brand\";v=\"24\"',\n",
    "            'sec-ch-ua-mobile': '?0',\n",
    "            'sec-ch-ua-platform': '\"Windows\"',\n",
    "            'sec-fetch-dest': 'empty',\n",
    "            'sec-fetch-mode': 'cors',\n",
    "            'sec-fetch-site': 'same-origin',\n",
    "            'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36',\n",
    "            'x-requested-with': 'XMLHttpRequest'\n",
    "        }\n",
    "\n",
    "        # 发送POST请求\n",
    "        response = session.post(url, data=data, headers=headers)\n",
    "        \n",
    "        # 检查请求是否成功\n",
    "        if response.status_code == 200:\n",
    "            print(\"Request successful\")\n",
    "        \n",
    "            # 解析更新面板HTML内容\n",
    "            \n",
    "            update_soup = BeautifulSoup(response.text, 'html.parser')\n",
    "                \n",
    "            # 查找并提取结果\n",
    "            result_bmi = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbbmi\")\n",
    "            result_major_osteoporotic = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbrs1\")\n",
    "            result_hip_fracture = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbrs2\")\n",
    "                \n",
    "            if result_bmi and result_major_osteoporotic and result_hip_fracture:\n",
    "                    print(\"BMI: \", result_bmi.text.strip())\n",
    "                    print(\"10-Year Major Osteoporotic Fracture Risk: \", result_major_osteoporotic.text.strip())\n",
    "                    print(\"10-Year Hip Fracture Risk: \", result_hip_fracture.text.strip())\n",
    "            else:\n",
    "                    print(\"Result not found\")\n",
    "        else:\n",
    "            print(\"错误发生\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据结构问题:\n",
    "输入4个连续变量(年龄体重等);实际也是离散变量 40-90岁 50个值 体重也假如有50个 ...一共50^4 \n",
    "\n",
    "8个布尔变量(0/1) 有无不良习惯(喝酒/抽烟...)共2^8\n",
    "\n",
    "现在我有一张表是输入12个(4+8)值得到一个输出,我如何搜索最快:哈希表 (k-d Tree) 或 Ball Tree 多维数组"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### T-score 最小二乘代码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "求得的参数 x = 1.039142552603529, y = 0.13983853776005684\n",
      "4.582838591290849\n",
      "-4.427553108899709\n",
      "-2.7\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "# 示例数据，包含多组 (t, b) 值\n",
    "t_values = np.array([-6, -5.3, -3.5, -2.2,-0.4,2,3])  # 替换为实际数据\n",
    "b_values = np.array([0.2, 0.3, 0.55, 0.73,0.98,1.32,1.46])  # 替换为实际数据\n",
    "\n",
    "# 构造矩阵 A 和向量 b\n",
    "A = np.vstack([t_values, np.ones(len(t_values))]).T\n",
    "B = b_values\n",
    "\n",
    "# 使用最小二乘法求解 y 和 x\n",
    "# np.linalg.lstsq 返回值:\n",
    "# coef：返回的是系数\n",
    "# residuals：残差平方和\n",
    "# rank：秩\n",
    "# s：奇异值\n",
    "coef, residuals, rank, s = np.linalg.lstsq(A, B, rcond=None)\n",
    "\n",
    "# 提取系数\n",
    "y, x = coef[0], coef[1]\n",
    "\n",
    "print(f\"求得的参数 x = {x}, y = {y}\")\n",
    "print((1.68-x)/y)\n",
    "#与网站上1.68 4.6相似\n",
    "print((0.42-x)/y) #4.4\n",
    "# 使用round函数保留小数一位\n",
    "print(round((0.66-x)/y,1))#和系统-2.7一样 ,实际上post并不限制小数位数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 循环枚举"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "输入数据顺序 年龄40-90  身高130.0-200.0 体重25.0-150  露娜0.20-1.87  性别\n",
    "\n",
    " 与 布尔变量:既往骨折史 父母 吸烟 ..风湿性 arth 继发性oste 喝大酒 共七个 ,\n",
    "输入框里是2.123和180.3 限制总共的位数\n",
    "输出结果 骨质疏松  髋部\n",
    "\n",
    "将上面的封装成函数  两个输出结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests,openpyxl\n",
    "from bs4 import BeautifulSoup\n",
    "import itertools\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write(age,hei,wei,luna,sex,prev,pfrac,smoker,glu,arth,oste,alco):\n",
    "   # 设置目标URL\n",
    "    url = \"https://frax.shef.ac.uk/FRAX/tool.aspx?country=2\"\n",
    "\n",
    "    # 使用requests会话\n",
    "    with requests.Session() as session:\n",
    "        # 获取初始页面以获取隐藏字段的值\n",
    "        initial_page = session.get(url)\n",
    "        x=1.039142552603529\n",
    "        y=0.13983853776005684\n",
    "        t=round((luna-x)/y,1)\n",
    "        # 检查请求是否成功\n",
    "        if initial_page.status_code == 200:\n",
    "            # 从页面中提取隐藏字段的值\n",
    "            soup = BeautifulSoup(initial_page.text, 'html.parser')\n",
    "            viewstate = soup.find(id='__VIEWSTATE')['value']\n",
    "            eventvalidation = soup.find(id='__EVENTVALIDATION')['value']\n",
    "            viewstategenerator = soup.find(id='__VIEWSTATEGENERATOR')['value']\n",
    "            hdn_auth_code = soup.find(id='ctl00_ContentPlaceHolder1_hdnAuthCode')['value']\n",
    "            \n",
    "            # 模拟表单数据\n",
    "            data = {\n",
    "                'ctl00$ScriptManager1': 'ctl00$ContentPlaceHolder1$updResult|ctl00$ContentPlaceHolder1$btnCalculate',\n",
    "                'ctl00_ScriptManager1_TSM': ';;System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35:en-US:18f1b484-bbc5-4e2e-8ca4-477603537f34:ea597d4b:b25378d2',\n",
    "                '__EVENTTARGET': '',\n",
    "                '__EVENTARGUMENT': '',\n",
    "                '__VIEWSTATE': viewstate,\n",
    "                '__VIEWSTATEGENERATOR': viewstategenerator,\n",
    "                '__EVENTVALIDATION': eventvalidation,\n",
    "                'language': 'en',\n",
    "    #             'country':'2',\n",
    "                'ctl00$ContentPlaceHolder1$hdnethnicity': '2',\n",
    "                'ctl00$ContentPlaceHolder1$hdnpreviousfracture': str(prev),##* 这些0/1是必填\n",
    "                'ctl00$ContentPlaceHolder1$hdnpfracturehip': str(pfrac),\n",
    "                'ctl00$ContentPlaceHolder1$hdncurrentsmoker': str(smoker),\n",
    "                'ctl00$ContentPlaceHolder1$hdnglucocorticoids': str(glu),\n",
    "                'ctl00$ContentPlaceHolder1$hdnarthritis': str(arth),\n",
    "                'ctl00$ContentPlaceHolder1$hdnosteoporosis': str(oste),\n",
    "                'ctl00$ContentPlaceHolder1$hdnalcohol': str(alco),\n",
    "                'ctl00$ContentPlaceHolder1$hdnbmd': 'T',\n",
    "                'ctl00$ContentPlaceHolder1$hdnthescore': str(t),#测试0.35 -5 0.55:-3.5##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$hdnsex': str(sex),##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$hdnbmi': '',\n",
    "                'ctl00$ContentPlaceHolder1$hdnAuthCode': hdn_auth_code,\n",
    "                'ctl00$ContentPlaceHolder1$nameid': '',\n",
    "                'ctl00$ContentPlaceHolder1$toolage': str(age),  # 示例年龄##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$toolagehidden': str(age),##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$year': '',\n",
    "                'ctl00$ContentPlaceHolder1$month': '',\n",
    "                'ctl00$ContentPlaceHolder1$day': '',\n",
    "                'ctl00$ContentPlaceHolder1$sex': 'female',  # 示例性别##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$toolweight': str(wei),  # 示例体重##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$toolweighthidden': str(wei),##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$ht': str(hei),  # 示例身高\n",
    "                'ctl00$ContentPlaceHolder1$toolheighthidden': str(hei),##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$facture': 'no',\n",
    "                'ctl00$ContentPlaceHolder1$facture_hip': 'yes',\n",
    "                'ctl00$ContentPlaceHolder1$smoking': 'yes',\n",
    "                'ctl00$ContentPlaceHolder1$glu': 'yes',\n",
    "                'ctl00$ContentPlaceHolder1$rhe_art': 'yes',\n",
    "                'ctl00$ContentPlaceHolder1$sec_ost': '这些yesno根本没用',\n",
    "                'ctl00$ContentPlaceHolder1$alcohol': 'no',\n",
    "                'dxa': 'GE-Lunar',  # 示例DXA##* 是必填\n",
    "                'ctl00$ContentPlaceHolder1$bmd_input': str(luna) , # 示例BMD值 \n",
    "                '__ASYNCPOST': 'true',\n",
    "                'ctl00$ContentPlaceHolder1$btnCalculate': 'Calculate'\n",
    "            }\n",
    "\n",
    "            # 设置请求头\n",
    "            headers = {\n",
    "                'authority': 'frax.shef.ac.uk',\n",
    "                'method': 'POST',\n",
    "                'path': '/FRAX/tool.aspx/GetCountryHits',\n",
    "                'scheme': 'https',\n",
    "                'accept': '*/*',\n",
    "                'accept-encoding': 'gzip, deflate, br, zstd',\n",
    "                'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8',\n",
    "                'content-length': str(len(str(data))),\n",
    "                'content-type': 'application/x-www-form-urlencoded; charset=UTF-8',\n",
    "                'cookie': 'country=2; lang=chs; ASP.NET_SessionId=rkpwszbi4jdfbv2wvhoztik2; b-user-id=2fa2a736-7a21-81a0-e74a-13c7b61df511; _ga=GA1.3.140746098.1717039493; _gid=GA1.3.240733912.1717039493; _ga_9QM6GC3ENW=GS1.3.1717045018.2.1.1717046357.0.0.0',\n",
    "                'origin': 'https://frax.shef.ac.uk',\n",
    "                'priority': 'u=1, i',\n",
    "                'referer': 'https://frax.shef.ac.uk/FRAX/tool.aspx',\n",
    "                'sec-ch-ua': '\"Google Chrome\";v=\"125\", \"Chromium\";v=\"125\", \"Not.A/Brand\";v=\"24\"',\n",
    "                'sec-ch-ua-mobile': '?0',\n",
    "                'sec-ch-ua-platform': '\"Windows\"',\n",
    "                'sec-fetch-dest': 'empty',\n",
    "                'sec-fetch-mode': 'cors',\n",
    "                'sec-fetch-site': 'same-origin',\n",
    "                'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36',\n",
    "                'x-requested-with': 'XMLHttpRequest'\n",
    "            }\n",
    "\n",
    "            # 发送POST请求\n",
    "            response = session.post(url, data=data, headers=headers)\n",
    "            p1=\"none\"\n",
    "            p2=\"none\"\n",
    "            # 检查请求是否成功\n",
    "            if response.status_code == 200:\n",
    "                print(\"Request successful\")\n",
    "            \n",
    "                # 解析更新面板HTML内容\n",
    "                \n",
    "                update_soup = BeautifulSoup(response.text, 'html.parser')\n",
    "                    \n",
    "                # 查找并提取结果\n",
    "                result_bmi = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbbmi\")\n",
    "                result_major_osteoporotic = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbrs1\")\n",
    "                result_hip_fracture = update_soup.find(id=\"ctl00_ContentPlaceHolder1_lbrs2\")\n",
    "                    \n",
    "                if result_bmi and result_major_osteoporotic and result_hip_fracture:\n",
    "                        if result_major_osteoporotic.text.strip():\n",
    "                            p1= result_major_osteoporotic.text.strip()\n",
    "                        else:\n",
    "                            p1=\"none\"\n",
    "                        if result_hip_fracture.text.strip():\n",
    "                            p2= result_hip_fracture.text.strip()\n",
    "                        else:\n",
    "                             p2=\"none\"\n",
    "                else:\n",
    "                        print(\"Result not found\")\n",
    "            else:\n",
    "                print(\"错误发生\")\n",
    "\n",
    "\n",
    "    wb = openpyxl.load_workbook(\"Frax.xlsx\")\n",
    "    \n",
    "    # 选择第一个工作表，或者根据需要选择特定工作表\n",
    "    ws = wb.active\n",
    "    \n",
    "    # 找到最后一行的下一行\n",
    "    next_row = ws.max_row + 1\n",
    "\n",
    "    # 写入参数值\n",
    "    values = [age, hei, wei, luna, sex, prev, pfrac, smoker, glu, arth, oste, alco, p1, p2]\n",
    "    for col_num, value in enumerate(values, 1):\n",
    "        ws.cell(row=next_row, column=col_num, value=value)\n",
    "    \n",
    "    # 保存工作簿\n",
    "    wb.save(\"Frax.xlsx\")\n",
    "    time.sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Frax.xlsx 文件已生成\n"
     ]
    }
   ],
   "source": [
    "import openpyxl\n",
    "\n",
    "# 创建一个新的工作簿\n",
    "wb = openpyxl.Workbook()\n",
    "\n",
    "# 选择默认的工作表\n",
    "sheet = wb.active\n",
    "\n",
    "# 为工作表命名\n",
    "sheet.title = \"Sheet1\"\n",
    "\n",
    "# 在第一行和第一列写入数据\n",
    "sheet['A1'] = 'Frax'\n",
    "\n",
    "# 保存工作簿\n",
    "wb.save(\"Frax.xlsx\")\n",
    "\n",
    "print(\"Frax.xlsx 文件已生成\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Result not found\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Result not found\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Request successful\n",
      "Result not found\n"
     ]
    }
   ],
   "source": [
    "# 用于生成浮点数范围的函数\n",
    "def frange(start, stop, step):\n",
    "    while start < stop:\n",
    "        yield start\n",
    "        start += step\n",
    "\n",
    "ages = range(40, 91)  # age从40到90，步长1\n",
    "heights = [round(hei, 1) for hei in frange(130.0, 200.0, 0.1)]  # hei从130.0到200.0，步长0.1\n",
    "weights = [round(wei, 1) for wei in frange(25.0, 150.0, 0.1)]  # wei从25.0到150.0，步长0.1\n",
    "lunas = [round(luna, 2) for luna in frange(0.20, 1.87, 0.01)]  # luna从0.20到1.87，步长0.01\n",
    "binary_values = [0, 1]  # sex及其后的参数，取值0或1\n",
    "\n",
    "\n",
    "# 生成所有组合\n",
    "combinations = itertools.product(\n",
    "    ages, heights, weights, lunas, binary_values, binary_values, binary_values,\n",
    "    binary_values, binary_values, binary_values, binary_values, binary_values\n",
    ")\n",
    "\n",
    "# 调用write函数\n",
    "for combination in combinations:\n",
    "    write(*combination)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (base)",
   "language": "python",
   "name": "base"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
